/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Modulo4;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.json.*;
import java.io.PrintWriter;
import Modelo.DAL.ConexionBD;
import Modelo.DAL.Guardia_DAO;
import java.sql.*;

/**
 *
 * @author yngrdyn
 */
public class listaCiReemplazo extends org.apache.struts.action.Action {

    /* forward name="success" path="" */
    private static final String SUCCESS = "success";

    /**
     * This is the action called from the Struts framework.
     * @param mapping The ActionMapping used to select this instance.
     * @param form The optional ActionForm bean for this request.
     * @param request The HTTP Request we are processing.
     * @param response The HTTP Response we are processing.
     * @throws java.lang.Exception
     * @return
     */
    @Override
    public ActionForward execute(ActionMapping mapping, ActionForm form,
            HttpServletRequest request, HttpServletResponse response)
            throws Exception {

        String fechaG = request.getParameter("fechaG");
        String ci = request.getParameter("ci");

        Guardia_DAO guardD = new Guardia_DAO();
        Long idGuardia = guardD.obtenerIdGuardia(fechaG, ci);

        System.out.println("pase");

        response.setContentType("text/json");
        JSONObject data = new JSONObject();

        data.put("ci", listaCiReemplazo(idGuardia));
        PrintWriter pw = response.getWriter();
        pw.write(data.toString());
        pw.flush();
        pw.close();

        return mapping.findForward(SUCCESS);
    }

      public JSONObject listaCiReemplazo(Long idGuardia){

        ConexionBD conexion = new ConexionBD();
        if (conexion.session == null)
            return null;
        Statement stt = null;
        //LinkedList list = new LinkedList();
        JSONObject listCi = new JSONObject();
        try{
            stt = conexion.session.createStatement();

            String query = "select cu.ci, cu.nombre_1, cu.apellido_1 "
                    + "from personal_dsi p, pertenece_grupo pp, guardia, comunidad_universitaria cu"
                    + " where p.ci = cu.ci and pp.ci = p.ci"
                    + " and pp.id_grupo not in"
                    + "(select id_grupo from guardia"
                    + " where id_guardia ="+idGuardia+")"
                    + " and pp.sede in"
                    + "(select sede from guardia"
                    + " where id_guardia ="+idGuardia+")"
                    + " and pp.id_grupo not in"
                    + "(select id_grupo from guardia"
                    + " where id_guardia not in"
                    + "(select id_guardia from guardia"
                    + " where fecha_guardia in"
                    + "(select fecha_guardia from guardia"
                    + " where id_guardia ="+idGuardia+")"
                    + " and id_guardia not in("+idGuardia+")))"
                    ;

            System.out.println("\n\n"+query+"\n\n\n");

            ResultSet rs = stt.executeQuery(query);
            int i=0;
            while(rs.next()){
                //Asistencia ast = new Asistencia();
                JSONObject ciPers = new JSONObject();

                //String horaLlegS = rs.getString("hora_llegada");
                //java.util.Date horaLlegD = java.sql.Date.valueOf(horaLlegS);

                ciPers.put("ci", rs.getString(1));
                ciPers.put("nombre_1", rs.getString(2));
                ciPers.put("apellido_1", rs.getString(3));
                //System.out.println("CI: "+ci);

                listCi.put(""+i++,ciPers);
            }
        } catch(SQLException se){
            System.out.println("Error al listar las cedulas de los empleados.\n"+
                    se.getMessage());
        } catch (Exception e){
            System.out.println("Error al listar las cedulas de los empleados.\n"+
                    e.getMessage());
        } finally {
            if (conexion.session !=null)
                conexion.cerrarConexion();
        }

        //return list;
        return listCi;
    }
}

